package data;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.Duration;
import javax.xml.datatype.XMLGregorianCalendar;

import edu.ipfw.fitness.EquipmentCategoryType;
import edu.ipfw.fitness.EquipmentIdListType;
import edu.ipfw.fitness.EquipmentType;
import edu.ipfw.fitness.EquipmentUsageListType;
import edu.ipfw.fitness.EquipmentUsageType;
import edu.ipfw.services.equipment.EquipmentNotFound;
import edu.ipfw.services.equipment.EquipmentNotFoundFault;

public class Db {

	// MySQL Database Connection
	Connection conn = null;

	// Initialize MySQL Database Connection when Constructor is called
	public Db() {

		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/DWWIG_Project", "root", "admin");

		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// Should be called after done with database calls
	public void closeDbConn() {
		try {
			conn.close();
		} catch (SQLException e) {
			// handle any errors
			e.printStackTrace();
		}
	}

	/*
	 * 
	 * addEquipment
	 * 
	 * 
	 * CREATE DEFINER=`root`@`localhost` PROCEDURE `addEquipment`(IN equipmentId
	 * VARCHAR(255), IN categoryId VARCHAR(255), IN description VARCHAR(255), IN
	 * lastMaintenanceDate timestamp) BEGIN INSERT INTO equipment (equipmentId,
	 * description, categoryId, totalMileage, totalTime, currentMileage,
	 * lastMaintenanceDate) VALUES (equipmentId, description, (select cid from
	 * eCategories where eCategory = categoryId), '0', '0', '0',
	 * lastMaintenanceDate); END
	 */
	public boolean addEquipment(String id, String description,
			String categoryId, XMLGregorianCalendar currentDateTime) {
		boolean result = false;

		try {
			// Call stored procedure, and use prepared statement to insert
			// values
			CallableStatement sprocs = conn
					.prepareCall("{call addEquipment(?,?,?,?)}");
			sprocs.setString(1, id);
			sprocs.setString(2, categoryId);
			sprocs.setString(3, description);
			// Date stuff is weird. Have to make a sql date from a java date
			java.sql.Date mySqlDate = new java.sql.Date(currentDateTime
					.toGregorianCalendar().getTimeInMillis());
			sprocs.setDate(4, mySqlDate);
			int a = sprocs.executeUpdate();
			// If a > 0, than the statement was successfully executed
			if (a > 0) {
				result = true;
			}
			sprocs.close();
			// System.out.println("Your data has been inserted into table.");

		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;

	}

	/*
	 * 
	 * 
	 * getEquipment
	 * 
	 * 
	 * CREATE DEFINER=`root`@`localhost` PROCEDURE `getEquipment`(IN id
	 * VARCHAR(255)) BEGIN SELECT
	 * e.equipmentId,e.description,c.eCategory,e.totalMileage
	 * ,e.totalTime,e.currentMileage,e.lastMaintenanceDate FROM equipment e,
	 * eCategories c WHERE e.equipmentId = id AND e.categoryId = c.cid; END
	 */

	public EquipmentType getEquipment(String id)
			throws DatatypeConfigurationException {

		EquipmentType equipment = new EquipmentType();

		try {
			// Call stored procedure, and use prepared statement to insert
			// values
			CallableStatement sprocs = conn
					.prepareCall("{call getEquipment(?)}");
			sprocs.setString(1, id);
			ResultSet rs = sprocs.executeQuery();
			while (rs.next()) {
				EquipmentCategoryType cat = null;
				// cat.fromValue(rs.getString("eCategory"));
				cat = EquipmentCategoryType.valueOf(rs.getString("eCategory"));

				equipment.setCategory(cat);
				equipment.setCurrentMileage(rs.getBigDecimal("currentMileage"));
				equipment.setDescription(rs.getString("description"));
				equipment.setId(id);

				GregorianCalendar gc = new GregorianCalendar();
				XMLGregorianCalendar dtf = null;
				gc.setTime(rs.getDate("lastMaintenanceDate"));
				dtf = DatatypeFactory.newInstance().newXMLGregorianCalendar(gc);

				equipment.setLastMaintenanceDate(dtf);
				equipment.setTotalMileage(rs.getBigDecimal("totalMileage"));

				// rs.getTime("totalTime").getTime();

				// PnYn MnDTnH nMnS, where nY represents the number of years, nM
				// the number of months, nD the number of days,
				// 'T' is the date/time separator, nH the number of hours, nM
				// the number of minutes and nS the number of seconds.
				Duration totalTime = DatatypeFactory.newInstance().newDuration(
						rs.getTime("totalTime").getTime());

				Duration currentTime = DatatypeFactory.newInstance()
						.newDuration(rs.getTime("currentTime").getTime());

				equipment.setTotalTime(totalTime);
				equipment.setCurrentTime(currentTime);
				equipment.setUsageHistory(getUsage(id));

			}

			sprocs.close();
			// System.out.println("Your data has been inserted into table.");

		} catch (SQLException e) {

			e.printStackTrace();
		}

		return equipment;

	}

	/*
	 * 
	 * getUsage
	 * 
	 * 
	 * CREATE PROCEDURE `DWWIG_Project`.`getUsage` (IN id VARCHAR(255)) BEGIN
	 * SELECT startTime,endTime FROM eUsageHistory WHERE eId = id; END
	 */

	// Returns EquipmentUsageListType object, takes equipmentId as arg.
	public EquipmentUsageListType getUsage(String equipmentId) {

		// Create new EquipmentUsageListType object to return
		EquipmentUsageListType equipment = new EquipmentUsageListType();

		try {
			// Call stored procedure, and use prepared statement to insert
			// values
			CallableStatement sprocs = conn.prepareCall("{call getUsage(?)}");
			sprocs.setString(1, equipmentId);
			ResultSet rs = sprocs.executeQuery();
			while (rs.next()) {
				// Create EquipmentUsageType to store in the list
				EquipmentUsageType usage = new EquipmentUsageType();
				// Iterate through result set, set start/stop

				GregorianCalendar gc = new GregorianCalendar();
				XMLGregorianCalendar dtf = null;
				gc.setTime(rs.getTimestamp("startTime"));
				try {
					dtf = DatatypeFactory.newInstance()
							.newXMLGregorianCalendar(gc);
				} catch (DatatypeConfigurationException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				usage.setStartTime(dtf);

				GregorianCalendar gcc = new GregorianCalendar();
				XMLGregorianCalendar dtff = null;
				gc.setTime(rs.getTimestamp("startTime"));
				try {
					dtff = DatatypeFactory.newInstance()
							.newXMLGregorianCalendar(gcc);
				} catch (DatatypeConfigurationException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				usage.setStopTime(dtff);

				// EquipmentUsageType e
				equipment.getUsageEntry().add(usage);
			}

			sprocs.close();

		} catch (SQLException e) {

			e.printStackTrace();
		}

		return equipment;
	}

	/*
	 * 
	 * 
	 * getEquipmentList
	 * 
	 * Retrieves all occurrences of Equipment of given category, excluding
	 * usageHistory, from persistent storage. If category is not present,
	 * retrieves all occurrences of Equipment.
	 * 
	 * 
	 * 
	 * 
	 * CREATE PROCEDURE `DWWIG_Project`.`getEquipmentList` (IN eCategory
	 * VARCHAR(255)) BEGIN SELECT e.equipmentId FROM equipment e, eCategories c
	 * WHERE c.cid = e.categoryId AND c.eCategory = eCategory; END
	 */

	public EquipmentIdListType getEquipmentList(EquipmentCategoryType category) {

		// Create new list to return equipmentId's
		EquipmentIdListType list = new EquipmentIdListType();

		// Check if a category was passed
		// If so, return equipment for that category
		// If not, return all equipment
		if (category == null) {
			// Return ALL equipment
			try {

				/*
				 * 
				 * CREATE PROCEDURE `DWWIG_Project`.`getEquipmentListAll` ()
				 * BEGIN SELECT equipmentId FROM equipment; END
				 */
				CallableStatement sprocs = conn
						.prepareCall("{call getEquipmentListAll()}");

				ResultSet rs = sprocs.executeQuery();

				// Loop through results and add equipmentId's to the list

				while (rs.next()) {
					// Just getting the equipmentId for now
					// Can be changed to get more if needed
					list.getId().add(rs.getString("equipmentId"));
				}

				sprocs.close();

				return list;

			} catch (SQLException e) {

				e.printStackTrace();
			}
		} else {
			// Return the equipment with the category given
			try {
				// Call stored procedure, and use prepared statement to insert
				// values
				CallableStatement sprocs = conn
						.prepareCall("{call getEquipmentList(?)}");
				sprocs.setString(1, category.toString());
				ResultSet rs = sprocs.executeQuery();

				// Loop through results and add equipmentId's to the list

				while (rs.next()) {
					// Just getting the equipmentId for now
					// Can be changed to get more if needed
					list.getId().add(rs.getString("equipmentId"));
				}

				sprocs.close();

			} catch (SQLException e) {

				e.printStackTrace();
			}
		}

		return list;

	}

	/*
	 * 
	 * 
	 * getMaintenanceDue
	 * 
	 * Returns usage list of Equipment Ids for Equipment due for maintenance.An
	 * Equipment is due for maintenance if either three months have passedsince
	 * lastMaintenanceDate or currentMileage is greater than or equal to 1000.
	 * 
	 * 
	 * 
	 * CREATE PROCEDURE `DWWIG_Project`.`getMaintenanceDue` () BEGIN SELECT
	 * equipmentId FROM equipment WHERE currentMileage >= '1000' OR
	 * DATEDIFF(CURDATE(),lastMaintenanceDate) >= '90'; END
	 */
	public EquipmentIdListType getMaintenanceDue() {

		EquipmentIdListType list = new EquipmentIdListType();
		try {
			// Call stored procedure, and use prepared statement to insert
			// values
			CallableStatement sprocs = conn
					.prepareCall("{call getMaintenanceDue()}");

			ResultSet rs = sprocs.executeQuery();

			// Loop through results and add equipmentId's to the list

			while (rs.next()) {
				// Just getting the equipmentId for now
				// Can be changed to get more if needed
				list.getId().add(rs.getString("equipmentId"));
			}

			sprocs.close();

		} catch (SQLException e) {

			e.printStackTrace();
		}

		return list;
	}

	public EquipmentUsageListType getUsageForDateRange(
			XMLGregorianCalendar startDate, XMLGregorianCalendar endDate,
			String id) {

		// Create new EquipmentUsageListType to return
		EquipmentUsageListType list = new EquipmentUsageListType();
		
		//Check if an id is passed
		//if not return all equipment in the date range
		if (id == null) {
			try {
				CallableStatement sprocs = conn
						.prepareCall("{call getUsageForDateRangeAll(?,?)}");

				// Convert startDate to sqlStartDate
//				java.sql.Date sqlStartDate = new java.sql.Date(startDate
//						.toGregorianCalendar().getTimeInMillis());

				// Convert endDate to sqlEndDate
//				java.sql.Date sqlEndDate = new java.sql.Date(endDate
//						.toGregorianCalendar().getTimeInMillis());

				//sprocs.setDate(1, sqlStartDate);
				sprocs.setTimestamp(1, new Timestamp(startDate.toGregorianCalendar().getTimeInMillis()));
				//sprocs.setDate(2, sqlEndDate);
				sprocs.setTimestamp(2, new Timestamp(endDate.toGregorianCalendar().getTimeInMillis()));
				//Not using ID, we are returning all usage
				//sprocs.setString(3, id);

				ResultSet rs = sprocs.executeQuery();

				// Loop through results and add equipmentId's to the list

				while (rs.next()) {
					// Create a new usage for each record
					EquipmentUsageType usage = new EquipmentUsageType();

					GregorianCalendar gc = new GregorianCalendar();
					XMLGregorianCalendar dtf = null;
					gc.setTime(rs.getTimestamp("startTime"));
					try {
						dtf = DatatypeFactory.newInstance()
								.newXMLGregorianCalendar(gc);
					} catch (DatatypeConfigurationException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}

					
					usage.setStartTime(dtf);

					gc.setTime(rs.getTimestamp("endTime"));
					try {
						dtf = DatatypeFactory.newInstance().newXMLGregorianCalendar(gc);
					} catch (DatatypeConfigurationException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}

					usage.setStopTime(dtf);
					// Add the new usage to the list.
				
					list.getUsageEntry().add(usage);
				}

				sprocs.close();

			} catch (SQLException e) {

				e.printStackTrace();
			}

		} else {
			//equipmentId was provided so only return usage for that equipment
			try {
				CallableStatement sprocs = conn
						.prepareCall("{call getUsageForDateRange(?,?,?)}");

				// Convert startDate to sqlStartDate
//				java.sql.Date sqlStartDate = new java.sql.Date(startDate
//						.toGregorianCalendar().getTimeInMillis());

				// Convert endDate to sqlEndDate
//				java.sql.Date sqlEndDate = new java.sql.Date(endDate
//						.toGregorianCalendar().getTimeInMillis());

				//sprocs.setDate(1, sqlStartDate);
				sprocs.setTimestamp(1, new Timestamp(startDate.toGregorianCalendar().getTimeInMillis()));
				//sprocs.setDate(2, sqlEndDate);
				sprocs.setTimestamp(2, new Timestamp(endDate.toGregorianCalendar().getTimeInMillis()));
				sprocs.setString(3, id);

				ResultSet rs = sprocs.executeQuery();

				// Loop through results and add equipmentId's to the list

				while (rs.next()) {
					// Create a new usage for each record
					EquipmentUsageType usage = new EquipmentUsageType();

					GregorianCalendar gc = new GregorianCalendar();
					XMLGregorianCalendar dtf = null;
					gc.setTime(rs.getTimestamp("startTime"));
					//System.out.println("START TIME: " + rs.getDate("startTime"));
					try {
						dtf = DatatypeFactory.newInstance()
								.newXMLGregorianCalendar(gc);
					} catch (DatatypeConfigurationException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}

					usage.setStartTime(dtf);

					gc.setTime(rs.getTimestamp("endTime"));
					try {
						dtf = DatatypeFactory.newInstance()
								.newXMLGregorianCalendar(gc);
					} catch (DatatypeConfigurationException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}

					usage.setStopTime(dtf);
					// Add the new usage to the list.
					list.getUsageEntry().add(usage);
				}

				sprocs.close();

			} catch (SQLException e) {

				e.printStackTrace();
			}

		}
		// Return the list
		return list;
	}

	/*
	 * 
	 * maintain
	 * 
	 * CREATE PROCEDURE `DWWIG_Project`.`maintain` (IN id VARCHAR(255), IN date
	 * DATE, IN initalCurrentMileage DECIMAL, IN time TIME) BEGIN UPDATE
	 * equipment SET lastMaintenanceDate = date, currentMileage =
	 * initialCurrentMileage, totalTime = time WHERE equipmentId = id; END
	 */

	@SuppressWarnings("deprecation")
	public boolean maintain(String id, XMLGregorianCalendar currentDateTime) {

		int result = 0;

		try {
			// Call stored procedure, and use prepared statement to insert
			// values
			CallableStatement sprocs = conn
					.prepareCall("{call maintain(?,?,?,?)}");

			sprocs.setString(1, id);

			// Set Last Maintenance Date to the current system date/time
			// equipmentStore.get(id).setLastMaintenanceDate(getCurrentDateTime());
			java.sql.Date mySqlDate = new java.sql.Date(currentDateTime
					.toGregorianCalendar().getTimeInMillis());
			sprocs.setDate(2, mySqlDate);

			// Set Current Mileage to 0
			BigDecimal initialCurrentMileage = new BigDecimal("0");
			// equipmentStore.get(id).setCurrentMileage(initialCurrentMileage);
			sprocs.setBigDecimal(3, initialCurrentMileage);

			// Set the Current Time to 0
			// equipmentStore.get(id).setCurrentTime(DatatypeFactory.newInstance().newDuration(0));
			sprocs.setTime(4, new Time(0, 0, 0));

			result = sprocs.executeUpdate();

			sprocs.close();

		} catch (SQLException e) {

			e.printStackTrace();
		}

		if (result > 0) {
			return true;
		} else
			return false;

	}

	/*
	 * Adds entry to usageHistory. Updates currentMileage, totalMileage,
	 * currentTime, and totalTime.
	 */
	public boolean recordUsage(String id, XMLGregorianCalendar startDateTime,
			XMLGregorianCalendar endDateTime, float miles)
			throws EquipmentNotFoundFault, DatatypeConfigurationException {

		int recordUsageResult = 0;
		int updateEquipmentUsageResult = 0;

		try {

			// Add entry to eUsageHistory table
			CallableStatement sprocs = conn
					.prepareCall("{call recordUsage(?,?,?)}");

			// Use the equipmentId passed in
			sprocs.setString(1, id);

			// start datetime to sql date
			//java.sql.Date sqlStartDateTime = new java.sql.Date(startDateTime
			//		.toGregorianCalendar().getTimeInMillis());
			//sprocs.setDate(2, sqlStartDateTime);
			//Needs setTimestamp and not setDate
			sprocs.setTimestamp(2, new Timestamp(startDateTime.toGregorianCalendar().getTimeInMillis()));
			// end datetime to sql date
			//java.sql.Date sqlEndDateTime = new java.sql.Date(endDateTime
			//		.toGregorianCalendar().getTimeInMillis());
			//sprocs.setDate(3, sqlEndDateTime);
			//Needs setTimestamp and not setDate
			sprocs.setTimestamp(3, new Timestamp(endDateTime.toGregorianCalendar().getTimeInMillis()));
			recordUsageResult = sprocs.executeUpdate();

			// Update equipment table with new currentMileage, totalMileage
			// currentTime, and totalTime
			CallableStatement sprocsEquip = conn
					.prepareCall("{call updateEquipmentUsage(?,?,?)}");

			// Update using this id
			sprocsEquip.setString(1, id);

			// find time difference in millis from the start and end
			long time = (endDateTime.toGregorianCalendar().getTimeInMillis() - startDateTime
					.toGregorianCalendar().getTimeInMillis());

			// time duration between startTime and endTime
			Duration totalTime = DatatypeFactory.newInstance()
					.newDuration(time);

			Time sqlTime = new Time(totalTime.getHours(),
					totalTime.getMinutes(), totalTime.getSeconds());

			System.out.println("hour: " + totalTime.getHours() + " minute: "
					+ totalTime.getMinutes() + " seconds: "
					+ totalTime.getSeconds());

			System.out.println("sqlTime: " + sqlTime.toString());

			sprocsEquip.setTime(2, sqlTime);
			// May need to check to make sure the correct value is going through
			System.out.println("float mileage: " + miles);
			BigDecimal currentMileage = BigDecimal.valueOf(miles);
			System.out.println("BigDecimal Mileage: " + currentMileage);

			sprocsEquip.setDouble(3, miles);

			updateEquipmentUsageResult = sprocsEquip.executeUpdate();

			sprocs.close();
			sprocsEquip.close();

		} catch (SQLException e) {

			e.printStackTrace();
		}

		if (recordUsageResult >= 1 && updateEquipmentUsageResult >= 1)
			return true;
		else
			return false;

	}

	/*
	 * 
	 * removeEquipment
	 * 
	 * CREATE DEFINER=`root`@`localhost` PROCEDURE `removeEquipment`(IN id
	 * VARCHAR(255)) BEGIN DELETE FROM equipment WHERE equipmentId = id; END
	 * 
	 * 
	 * NOTE: Only removes the equipmentId from the equipment table currently
	 * doens't do anything to the eUsageHistory table for that piece of
	 * equipment. If needed this can be updated to also remove the equipment's
	 * usage history as well if we are removing the equipment.
	 */
	public boolean removeEquipment(String id) {

		int result = 0;

		try {
			// Call stored procedure, and use prepared statement to delete
			// values
			CallableStatement sprocs = conn
					.prepareCall("{call removeEquipment(?)}");

			sprocs.setString(1, id);

			result = sprocs.executeUpdate();

			sprocs.close();

		} catch (SQLException e) {

			e.printStackTrace();
		}

		if (result > 0) {
			return true;
		} else
			return false;

	}

}